package com.may.wlrj.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.may.wlrj.entity.UserLogEntity;
import com.may.wlrj.entity.LockEntity;
import com.may.wlrj.entity.LockOffEntity;
import com.may.wlrj.entity.LockOpenLogEntity;
import com.may.wlrj.entity.UserOperLogEntity;


public class ReadExcel {
	private Logger log = Logger.getLogger(ReadExcel.class);
    //总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 
    //错误信息接收器
    private String errorMsg;
    //构造方法
    public ReadExcel(){}
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    //获取错误信息
    public String getErrorInfo() { return errorMsg; }  
    
 // @描述：是否是2003的excel，返回true是2003 
    public boolean isExcel2003(String filePath)  {  
         return filePath.matches("^.+\\.(?i)(xls)$");  
     }  
   
    //@描述：是否是2007的excel，返回true是2007 
    public boolean isExcel2007(String filePath)  {  
         return filePath.matches("^.+\\.(?i)(xlsx)$");  
     }  
    
  /**
   * 验证EXCEL文件
   * @param filePath
   * @return
   */
  public boolean validateExcel(String filePath){
        if (filePath == null || !filePath.matches("^.+\\.(?i)(xls)$") || filePath.matches("^.+\\.(?i)(xlsx)$")){  
            errorMsg = "文件名不是excel格式";  
            log.error("[ERR]==============" + errorMsg);
            return false;  
        }  
        return true;
  }
    
  
  public void writeUserManagerlogsExcel(List<UserOperLogEntity> loglist,String[] titles, ServletOutputStream out){
	  try{
		// 第一步，创建一个workbook，对应一个Excel文件
		  SXSSFWorkbook  workbook = new SXSSFWorkbook ();
          
          // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		  SXSSFSheet hssfSheet = null;//workbook.createSheet("sheet1");
          
          // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
          
		  Row row = null;
         // 第四步，创建单元格，并设置值表头 设置表头居中
		  CellStyle hssfCellStyle = workbook.createCellStyle();

		  Cell hssfCell = null;
          
		  UserOperLogEntity cp = null;
          int rowNo = 0; // 总行号
		  int pageRowNo = 0; // 页行号		  

          for(int i = 0; i < loglist.size(); i++){
        	  if (rowNo % 300000 == 0) {
	  				System.out.println("当前sheet页为:" + rowNo / 300000 );
	  				hssfSheet = workbook.createSheet("sheet" + (rowNo / 300000 + 1));// 建立新的sheet对象
	  				hssfSheet = workbook.getSheetAt(rowNo / 300000); // 动态指定当前的工作表
	  				pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为1
	  				
	  			    //定义表头
	  				row = hssfSheet.createRow(0);
	  				for (int j = 0; j < titles.length; j++) {
	  		              hssfCell = row.createCell(j);//列索引从0开始
	  		              hssfCell.setCellValue(titles[j]);//列名1
	  		              hssfCell.setCellStyle(hssfCellStyle);//列居中显示     
	  		              hssfSheet.setColumnWidth(j, 21*256);
	  		          }
	  		  }
        	  rowNo++;
        	  row = hssfSheet.createRow(pageRowNo++); 
        	  cp = loglist.get(i);
        	  row.createCell(0).setCellValue(cp.getRname());
        	  row.createCell(1).setCellValue(cp.getUsername());
        	  row.createCell(2).setCellValue(cp.getOrgname());
        	  row.createCell(3).setCellValue(cp.getOperator());
        	  row.createCell(4).setCellValue(cp.getCheckname());
        	  row.createCell(5).setCellValue(cp.getCreatetime());
        	  row.createCell(6).setCellValue(cp.getDescriptor());
          }
          // 第七步，将文件输出到客户端浏览器
          try {
              workbook.write(out);
              out.flush();
              out.close();
              workbook.close();
          } catch (Exception e) {
              e.printStackTrace();
              log.error(e.getMessage());
          }
	  }catch(Exception e){
		  e.printStackTrace();
		  log.error(e.getMessage());
	  }
  }

  public void writeSyslogsExcel(List<UserLogEntity> loglist,String[] titles, ServletOutputStream out){
	  try{
		// 第一步，创建一个workbook，对应一个Excel文件
		  SXSSFWorkbook  workbook = new SXSSFWorkbook ();
          
          // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		  SXSSFSheet hssfSheet = null;//workbook.createSheet("sheet1");
          
          // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
          
		  Row row = null;
         // 第四步，创建单元格，并设置值表头 设置表头居中
		  CellStyle hssfCellStyle = workbook.createCellStyle();

		  Cell hssfCell = null;
          
		  UserLogEntity cp = null;
          int rowNo = 0; // 总行号
		  int pageRowNo = 0; // 页行号	
          for(int i = 0; i < loglist.size(); i++){
        	  if (rowNo % 300000 == 0) {
	  				System.out.println("当前sheet页为:" + rowNo / 300000 );
	  				hssfSheet = workbook.createSheet("sheet" + (rowNo / 300000 + 1));// 建立新的sheet对象
	  				hssfSheet = workbook.getSheetAt(rowNo / 300000); // 动态指定当前的工作表
	  				pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为1
	  				hssfSheet.trackAllColumnsForAutoSizing();
	  			    //定义表头
	  				row = hssfSheet.createRow(0);
	  				for (int j = 0; j < titles.length; j++) {
	  		              hssfCell = row.createCell(j);//列索引从0开始
	  		              hssfCell.setCellValue(titles[j]);//列名1
	  		              hssfCell.setCellStyle(hssfCellStyle);//列居中显示      
	  		              hssfSheet.setColumnWidth(j, 21*256);
	  		          }
	  		  }
        	  rowNo++;
        	  row = hssfSheet.createRow(pageRowNo++); 
        	  cp = loglist.get(i);
        	  row.createCell(0).setCellValue(cp.getCreatetime());
        	  row.createCell(1).setCellValue(cp.getCreateusername());
        	  row.createCell(2).setCellValue(cp.getRemote_addr());
        	  row.createCell(3).setCellValue(cp.getRname());
        	  row.createCell(4).setCellValue(cp.getOrgname());
          }
          // 第七步，将文件输出到客户端浏览器
          try {
              workbook.write(out);
              out.flush();
              out.close();
              workbook.close();
          } catch (Exception e) {
              e.printStackTrace();
              log.error(e.getMessage());
          }
	  }catch(Exception e){
		  e.printStackTrace();
		  log.error(e.getMessage());
	  }
  }
  
  public void writeOpenlogsExcel(List<LockOpenLogEntity> loglist,String[] titles, ServletOutputStream out){
	  try{
		// 第一步，创建一个workbook，对应一个Excel文件
		  SXSSFWorkbook  workbook = new SXSSFWorkbook ();
          
          // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		  SXSSFSheet hssfSheet = null;//workbook.createSheet("sheet1");
          
          // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
          
		  Row row = null;
         // 第四步，创建单元格，并设置值表头 设置表头居中
		  CellStyle hssfCellStyle = workbook.createCellStyle();

		  Cell hssfCell = null;
		  SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		  LockOpenLogEntity cp = null;
          int rowNo = 0; // 总行号
		  int pageRowNo = 0; // 页行号	
          for(int i = 0; i < loglist.size(); i++){
        	  if (rowNo % 300000 == 0) {
	  				System.out.println("当前sheet页为:" + rowNo / 300000 );
	  				hssfSheet = workbook.createSheet("sheet" + (rowNo / 300000 + 1));// 建立新的sheet对象
	  				hssfSheet = workbook.getSheetAt(rowNo / 300000); // 动态指定当前的工作表
	  				pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为1
	  				hssfSheet.trackAllColumnsForAutoSizing();
	  			    //定义表头
	  				row = hssfSheet.createRow(0);
	  				for (int j = 0; j < titles.length; j++) {
	  		              hssfCell = row.createCell(j);//列索引从0开始
	  		              hssfCell.setCellValue(titles[j]);//列名1
	  		              hssfCell.setCellStyle(hssfCellStyle);//列居中显示      
	  		              hssfSheet.setColumnWidth(j, 21*256);
	  		          }
	  		  }
        	  rowNo++;
        	  row = hssfSheet.createRow(pageRowNo++); 
        	  cp = loglist.get(i);
        	  row.createCell(0).setCellValue(cp.getLocksn());
        	  row.createCell(1).setCellValue(cp.getKeysn());
        	  row.createCell(2).setCellValue(cp.getOper_reals());
        	  row.createCell(3).setCellValue(sdf.format(cp.getOpentime()));
        	  if(cp.getMode() == 1) {
        		  row.createCell(4).setCellValue("开锁");
        	  }else {
        		  row.createCell(4).setCellValue("闭锁");
        	  }
        	  row.createCell(5).setCellValue(cp.getOrgname());
        	  row.createCell(6).setCellValue(cp.getProceed());
          }
          // 第七步，将文件输出到客户端浏览器
          try {
              workbook.write(out);
              out.flush();
              out.close();
              workbook.close();
          } catch (Exception e) {
              e.printStackTrace();
              log.error(e.getMessage());
          }
	  }catch(Exception e){
		  e.printStackTrace();
		  log.error(e.getMessage());
	  }
  }

  public void writeLockofflogsExcel(List<LockOffEntity> loglist,String[] titles, ServletOutputStream out){
	  try{
		// 第一步，创建一个workbook，对应一个Excel文件
		  SXSSFWorkbook  workbook = new SXSSFWorkbook ();
          
          // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		  SXSSFSheet hssfSheet = null;//workbook.createSheet("sheet1");
          
          // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
          
		  Row row = null;
         // 第四步，创建单元格，并设置值表头 设置表头居中
		  CellStyle hssfCellStyle = workbook.createCellStyle();

		  Cell hssfCell = null;
		  //SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		  LockOffEntity cp = null;
          int rowNo = 0; // 总行号
		  int pageRowNo = 0; // 页行号	
          for(int i = 0; i < loglist.size(); i++){
        	  if (rowNo % 300000 == 0) {
	  				System.out.println("当前sheet页为:" + rowNo / 300000 );
	  				hssfSheet = workbook.createSheet("sheet" + (rowNo / 300000 + 1));// 建立新的sheet对象
	  				hssfSheet = workbook.getSheetAt(rowNo / 300000); // 动态指定当前的工作表
	  				pageRowNo = 1; // 每当新建了工作表就将当前工作表的行号重置为1
	  				hssfSheet.trackAllColumnsForAutoSizing();
	  			    //定义表头
	  				row = hssfSheet.createRow(0);
	  				for (int j = 0; j < titles.length; j++) {
	  		              hssfCell = row.createCell(j);//列索引从0开始
	  		              hssfCell.setCellValue(titles[j]);//列名1
	  		              hssfCell.setCellStyle(hssfCellStyle);//列居中显示      
	  		              hssfSheet.setColumnWidth(j, 21*256);
	  		          }
	  		  }
        	  rowNo++;
        	  row = hssfSheet.createRow(pageRowNo++); 
        	  cp = loglist.get(i);
        	  row.createCell(0).setCellValue(cp.getLocksn());
        	  if(cp.getLocktype() == 1) {
        		  row.createCell(1).setCellValue("主管锁");
        	  }else if(cp.getLocktype() == 2) {
        		  row.createCell(1).setCellValue("柜员锁");
        	  }else if(cp.getLocktype() == 3) {
        		  row.createCell(1).setCellValue("调拨锁");
        	  }else {
        		  row.createCell(1).setCellValue("未知类型");
        	  }
        	  row.createCell(2).setCellValue(cp.getOper_reals());
        	  row.createCell(3).setCellValue(cp.getOrgname());
        	  row.createCell(4).setCellValue(cp.getArea());
        	  row.createCell(5).setCellValue(cp.getBoxid());
        	  row.createCell(6).setCellValue(cp.getOfftime());
        	  row.createCell(7).setCellValue(cp.getOffuser());
        	  row.createCell(8).setCellValue(cp.getDescriptor());
          }
          // 第七步，将文件输出到客户端浏览器
          try {
              workbook.write(out);
              out.flush();
              out.close();
              workbook.close();
          } catch (Exception e) {
              e.printStackTrace();
              log.error(e.getMessage());
          }
	  }catch(Exception e){
		  e.printStackTrace();
		  log.error(e.getMessage());
	  }
  }

}
